package controller.admin;

import com.jfoenix.controls.JFXButton;
import controller.Items.student;
import controller.Items.teacher;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.fxml.FXML;
import javafx.scene.Scene;
import javafx.scene.control.*;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.control.cell.TextFieldTableCell;
import javafx.scene.layout.AnchorPane;
import javafx.scene.text.Text;
import javafx.stage.Stage;
import sample.Admin.AdminAddStu;
import sql.JDBC_T;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.MessageFormat;
import java.util.Vector;
import java.util.regex.*;

public class AdminStuController {
    @FXML
    private Button adm_btn_search;
    @FXML
    private TextField adm_txfld_search;
    @FXML
    private TableView adm_tblList_stu;
    @FXML
    private Button adm_btn_addstu;
//    @FXML
//    private
    private Alert alertwindow;
    private AnchorPane anchorPane;
    private TableView tableView;
    private JDBC_T jdbc_t;
    public void initialize() throws SQLException {
       update(this.adm_tblList_stu);



    }
    public void update(TableView tableView) throws SQLException{
        String sql = "select stuinfo.SID,stuinfo.Sname,stuinfo.Ssex,stuinfo.Class,stuinfo.Dnum,stuinfo.Rnum\n" +
                "FROM stuinfo\n" +
                "GROUP BY stuinfo.SID ORDER BY stuinfo.Class ;";
        JDBC_T jdbc_t = new JDBC_T();
        String id,name,sex,clas,broom,room;
        //这个窗口特殊的容器类
        ObservableList<student> students = FXCollections.observableArrayList();
        Vector<String> ids=new Vector<>();
//        students.clear();
        if (jdbc_t.is_conn()){
            System.out.println("查询学生成功!!!");
            PreparedStatement preparedStatement = jdbc_t.getConn().prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                id=resultSet.getString("SID");
                name = resultSet.getString("Sname");
                sex =resultSet.getString("Ssex");
                clas = resultSet.getString("Class");
                broom =resultSet.getString("Dnum");
                room=resultSet.getString("Rnum");
                students.add(new student(id,name,sex,clas,broom,room));
                ids.add(id);
            }


            ObservableList<TableColumn> cols=tableView.getColumns();
            TableColumn sid = cols.get(0);
            TableColumn sname = cols.get(1);
            TableColumn  ssex = cols.get(2);
            TableColumn  sclass = cols.get(3);
            TableColumn  sbnum = cols.get(4);
            TableColumn  srnum = cols.get(5);
            TableColumn edit = cols.get(6);
            sid.setCellValueFactory(new PropertyValueFactory<>("id"));
            sname.setCellValueFactory(new PropertyValueFactory<>("name"));
            ssex.setCellValueFactory(new PropertyValueFactory<>("sex"));
            sclass.setCellValueFactory(new PropertyValueFactory<>("clas"));
            srnum.setCellValueFactory(new PropertyValueFactory<>("rnum"));
            sbnum.setCellValueFactory(new PropertyValueFactory<>("bnum"));
            //控件列
            TableColumn modify = (TableColumn) edit.getColumns().get(0);
            TableColumn delete = (TableColumn) edit.getColumns().get(1);
            modify.setCellFactory((col) -> {TableCell<student, String> cell = new TableCell<student, String>() {
                @Override
                protected void updateItem(String item, boolean empty) {
                    super.updateItem(item, empty);
                    Button button = new Button("修改");
                    button.setOnAction((col)->{
                        String sql ="";
                        student stu =students.get(getIndex());
                        if(ids.contains(stu.getId())) {
                            sql = MessageFormat.format("update stuinfo  set Sname=\"{0}\",Ssex=\"{1}\", MID=\"{2}\", Dnum=\"{3}\" ,Rnum=\"{4}\",Class=\"{5}\""
                                    + " where SID = \"{6}\"", stu.getName(), stu.getSex(), "00" + stu.getClas().charAt(0), stu.getBnum(),stu.getRnum(), stu.getClas() , stu.getId());
                            System.out.println(sql);
                            try {
                                preparedStatement.execute(sql);
                            } catch (SQLException sqlException) {
                                sqlException.printStackTrace();
                            }
                        }else{
                            alertwindow= new Alert(Alert.AlertType.WARNING);
                            alertwindow.setContentText("请注意是否修改了id");
                            alertwindow.showAndWait();

                        }
                    }
                    );
                    if (empty) {
                        //如果此列为空默认不添加元素
                        setText(null);
                        setGraphic(null); }
                    else { this.setGraphic(button); }
                }
            };
            return cell;
            });
            delete.setCellFactory((col)-> {TableCell<student, String> cell = new TableCell<student, String>() {
                @Override
                protected void updateItem(String item, boolean empty) {
                    super.updateItem(item, empty);
                    Button button = new Button("删除");
                    button.setOnAction((col)->{
                        System.out.println(getIndex());
                        student del_stu = students.get(getIndex());
                        String sid1 = del_stu.getId();
                        String sql1 = MessageFormat.format("Delete from stuinfo where SID = {0}{1}{0}",'"',sid1);
                        try {
                            preparedStatement.execute(sql1);
                        } catch (SQLException sqlException) {
                            sqlException.printStackTrace();
                        }
                        System.out.println(sql1);
                        students.remove(del_stu);
                            }
                    );
                    if (empty) {
                        //如果此列为空默认不添加元素
                        setText(null);
                        setGraphic(null); }
                    else { this.setGraphic(button); }
                }
            };
                return cell;
            });
            tableView.setItems(students);
            tableView.setEditable(true);
            sid.setCellFactory(TextFieldTableCell.forTableColumn());
            sname.setCellFactory(TextFieldTableCell.forTableColumn());
            ssex.setCellFactory(TextFieldTableCell.forTableColumn());
            sclass.setCellFactory(TextFieldTableCell.forTableColumn());
            sbnum.setCellFactory(TextFieldTableCell.forTableColumn());
            srnum.setCellFactory(TextFieldTableCell.forTableColumn());
//            srnum.setCellValueFactory(TextFieldTableCell.forTableColumn());


        }
    }
    public  void search() throws SQLException {
        Stage newstage = new Stage();
        anchorPane =new AnchorPane();
        tableView = new TableView();
        String search = adm_txfld_search.getText();
        String id,name,sex,clas,broom,room;
        ObservableList<student> students = FXCollections.observableArrayList();
        String sql = "";
        String reg1 = "^\\d{12}";
        String reg2 = "^[123]\\d";
        alertwindow = new Alert(Alert.AlertType.WARNING);
        boolean ismatch;
        if(search.isEmpty()){
            alertwindow.setContentText("输入为空!!!");
            alertwindow.showAndWait();
            return;
        }
        if(search.length()==12&&Pattern.matches(reg1,search)){
            //学号
             sql  =MessageFormat.format("select stuinfo.SID,stuinfo.Sname,stuinfo.Ssex,stuinfo.Class,stuinfo.Dnum,stuinfo.Rnum FROM stuinfo " +
                     " WHERE stuinfo.Sid = {0}{1}{0} " +
                     " GROUP BY stuinfo.SID","'",search);

        }else if(search.length()<=4){
            sql=MessageFormat.format("select stuinfo.SID,stuinfo.Sname,stuinfo.Ssex,stuinfo.Class,stuinfo.Dnum,stuinfo.Rnum" + " FROM stuinfo" +
                    " WHERE stuinfo.Sname = {0}{1}{0} " +
                    " GROUP BY stuinfo.SID ","'",search);
            if(search.length()==2 && Pattern.matches(reg2,search)){
                sql = MessageFormat.format("select stuinfo.SID,stuinfo.Sname,stuinfo.Ssex,stuinfo.Class,stuinfo.Dnum,stuinfo.Rnum  FROM stuinfo" +
                        " WHERE stuinfo.Class = {0}{1}{0} " +
                        " GROUP BY stuinfo.SID ",'"',search);
            }

        }
        else {
            alertwindow.setContentText("请输入12位学号或者2位班级号或学生名字");
            alertwindow.showAndWait();
            return;
        }

        System.out.println(sql);
        jdbc_t = new JDBC_T();
        if(jdbc_t.is_conn()){
            System.out.println("查找学生连接成功!!!");
            PreparedStatement preparedStatement = jdbc_t.getConn().prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(!resultSet.next()){
                alertwindow.setContentText("没有结果!!!");
                alertwindow.showAndWait();
                return;
            }
            else{
                id=resultSet.getString("SID");
                name = resultSet.getString("Sname");
                sex =resultSet.getString("Ssex");
                clas = resultSet.getString("Class");
                broom =resultSet.getString("Dnum");
                room=resultSet.getString("Rnum");
                students.add(new student(id,name,sex,clas,broom,room));
            }
            TableColumn sid = new TableColumn("学号");
            TableColumn sname = new TableColumn("姓名");
            TableColumn  ssex = new TableColumn("性别");
            TableColumn  sclass = new TableColumn("班级");
            TableColumn  sbnum =new TableColumn("楼号");
            TableColumn  srnum = new TableColumn("寝室号");
            sid.setCellValueFactory(new PropertyValueFactory<>("id"));
            sname.setCellValueFactory(new PropertyValueFactory<>("name"));
            ssex.setCellValueFactory(new PropertyValueFactory<>("sex"));
            sclass.setCellValueFactory(new PropertyValueFactory<>("clas"));
            srnum.setCellValueFactory(new PropertyValueFactory<>("rnum"));
            sbnum.setCellValueFactory(new PropertyValueFactory<>("bnum"));
            tableView.getColumns().addAll(sid,sname,ssex,sclass,sbnum,srnum);
            tableView.setItems(students);
        }
        anchorPane.setPrefWidth(300);
        anchorPane.setPrefHeight(300);
        anchorPane.getChildren().add(tableView);
        Scene sc =new Scene(anchorPane);
        newstage.setScene(sc);
        newstage.show();
    }
    public void addstu() throws Exception {
        new AdminAddStu().start(new Stage());
    }


}
